﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using IOEntity;
using System.Data;

namespace IODataAccess
{
    public class clsDATheLoai
    {
        public List<clsTheLoai> GetByQuocGia(int id)
        {
            clsDAConnection con = new clsDAConnection();
            try
            {
                List<clsTheLoai> list = new List<clsTheLoai>();
                string str = "select * from tblTheLoai where maquocgia=@maquocgia and madanhmuc<>1";
                SqlCommand com = new SqlCommand(str, con.getCon());
                com.Parameters.AddWithValue("@maquocgia", id);
                con.openCon();
                SqlDataReader dr = com.ExecuteReader();
                while (dr.Read())
                {
                    clsTheLoai obj = new clsTheLoai();
                    if (dr["tentheloai"] != DBNull.Value)
                    {
                        obj.TenTheLoai = dr["tentheloai"].ToString();
                    }
                    if (dr["stt"] != DBNull.Value)
                    {
                        obj.Stt = Convert.ToInt32(dr["stt"].ToString());
                    }
                    if (dr["MaQuocGia"] != DBNull.Value)
                    {
                        obj.QuocGia.Id = Convert.ToInt32(dr["MaQuocGia"].ToString());
                    }
                    if (dr["MaDanhMuc"] != DBNull.Value)
                    {
                        obj.DanhMuc.Id = Convert.ToInt32(dr["MaDanhMuc"].ToString());
                    }
                    if (dr["id"] != DBNull.Value)
                    {
                        obj.Id = Convert.ToInt32(dr["id"].ToString());
                    }
                    list.Add(obj);
                }
                con.closeCon();
                return list;
            }
            catch (Exception)
            {
                throw;
            }
            finally 
            {
                con.closeCon();
            }
            
        }
        public List<clsTheLoai> GetByQuocGia2(int id)
        {
            clsDAConnection con = new clsDAConnection();
            try
            {
                List<clsTheLoai> list = new List<clsTheLoai>();
                string str = "select * from tblTheLoai where maquocgia=@maquocgia and madanhmuc<>1 and hot=1";
                SqlCommand com = new SqlCommand(str, con.getCon());
                com.Parameters.AddWithValue("@maquocgia", id);
                con.openCon();
                SqlDataReader dr = com.ExecuteReader();
                while (dr.Read())
                {
                    clsTheLoai obj = new clsTheLoai();
                    if (dr["tentheloai"] != DBNull.Value)
                    {
                        obj.TenTheLoai = dr["tentheloai"].ToString();
                    }
                    if (dr["stt"] != DBNull.Value)
                    {
                        obj.Stt = Convert.ToInt32(dr["stt"].ToString());
                    }
                    if (dr["MaQuocGia"] != DBNull.Value)
                    {
                        obj.QuocGia.Id = Convert.ToInt32(dr["MaQuocGia"].ToString());
                    }
                    if (dr["MaDanhMuc"] != DBNull.Value)
                    {
                        obj.DanhMuc.Id = Convert.ToInt32(dr["MaDanhMuc"].ToString());
                    }
                    if (dr["id"] != DBNull.Value)
                    {
                        obj.Id = Convert.ToInt32(dr["id"].ToString());
                    }
                    list.Add(obj);
                }
                con.closeCon();
                return list;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.closeCon();
            }

        }
        public List<clsTheLoai> GetByQuocGia1(int id)
        {
            clsDAConnection con = new clsDAConnection();
            try
            {
                List<clsTheLoai> list = new List<clsTheLoai>();
                string str = "select * from tblTheLoai where maquocgia=@maquocgia";
                SqlCommand com = new SqlCommand(str, con.getCon());
                com.Parameters.AddWithValue("@maquocgia", id);
                con.openCon();
                SqlDataReader dr = com.ExecuteReader();
                while (dr.Read())
                {
                    clsTheLoai obj = new clsTheLoai();
                    if (dr["tentheloai"] != DBNull.Value)
                    {
                        obj.TenTheLoai = dr["tentheloai"].ToString();
                    }
                    if (dr["stt"] != DBNull.Value)
                    {
                        obj.Stt = Convert.ToInt32(dr["stt"].ToString());
                    }
                    if (dr["MaQuocGia"] != DBNull.Value)
                    {
                        obj.QuocGia.Id = Convert.ToInt32(dr["MaQuocGia"].ToString());
                    }
                    if (dr["MaDanhMuc"] != DBNull.Value)
                    {
                        obj.DanhMuc.Id = Convert.ToInt32(dr["MaDanhMuc"].ToString());
                    }
                    if (dr["id"] != DBNull.Value)
                    {
                        obj.Id = Convert.ToInt32(dr["id"].ToString());
                    }
                    list.Add(obj);
                }
                con.closeCon();
                return list;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.closeCon();
            }

        }
        public clsTheLoai GetById(int id)
        {
            clsDAConnection con = new clsDAConnection();
            try
            {
                clsTheLoai obj = new clsTheLoai();
                string str = "select * from tblTheLoai where id=@id";
                SqlCommand com = new SqlCommand(str, con.getCon());
                com.Parameters.AddWithValue("@id", id);
                con.openCon();
                SqlDataReader dr = com.ExecuteReader();
                if (dr.Read())
                {
                    
                    if (dr["tentheloai"] != DBNull.Value)
                    {
                        obj.TenTheLoai = dr["tentheloai"].ToString();
                    }
                    if (dr["stt"] != DBNull.Value)
                    {
                        obj.Stt = Convert.ToInt32(dr["stt"].ToString());
                    }
                    if (dr["MaQuocGia"] != DBNull.Value)
                    {
                        obj.QuocGia.Id = Convert.ToInt32(dr["MaQuocGia"].ToString());
                    }
                    if (dr["MaDanhMuc"] != DBNull.Value)
                    {
                        obj.DanhMuc.Id = Convert.ToInt32(dr["MaDanhMuc"].ToString());
                    }
                    if (dr["id"] != DBNull.Value)
                    {
                        obj.Id = Convert.ToInt32(dr["id"].ToString());
                    }
                }
                con.closeCon();
                return obj;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.closeCon();
            }

        }
        public bool InsertTL(clsTheLoai dm)
        {
            string sql = "Insert into tblTheLoai(TenTheLoai,MaDanhMuc,MaQuocGia,MoTa,STT,Hot) values(@TenTheLoai,@MaDanhMuc,@MaQuocGia,@MoTa,@STT,@Hot)";
            bool st = false;
            clsDAConnection conn = new clsDAConnection();
            conn.openCon();
            using (SqlCommand com = conn.getCon().CreateCommand())
            {
                com.CommandType = CommandType.Text;
                com.CommandText = sql;

                // Create a SqlParameter object for the title parameter.
                SqlParameter p1 = com.CreateParameter();
                p1.ParameterName = "@TenTheLoai";
                p1.SqlDbType = SqlDbType.NVarChar;
                p1.Size = 50;
                p1.Value = dm.TenTheLoai;
                com.Parameters.Add(p1);

                // Use a shorthand syntax to add the id parameter.
                com.Parameters.Add("@MaDanhMuc", SqlDbType.Int).Value = dm.MyDanhMuc;
                com.Parameters.Add("@MaQuocGia", SqlDbType.Int).Value = dm.MyQuocGia;
                com.Parameters.Add("@Mota", SqlDbType.NVarChar, 1000).Value = dm.MoTa;
                com.Parameters.Add("@STT", SqlDbType.Int).Value = dm.Stt;
                com.Parameters.Add("@Hot", SqlDbType.Int).Value = dm.Hot;
                // Execute the command and process the result.
                if (com.ExecuteNonQuery() == 1)
                {
                    st = true;
                }
                conn.closeCon();
                return st;
            }
        }

        // Sua danh muc
        public bool EditTheLoai(clsTheLoai dm)
        {
            string sql = "Update tblTheloai set TenTheLoai=@TenTheLoai,MaDanhMuc=@MaDanhMuc,MaQuocGia=@MaQuocGia,Mota=@Mota,STT=@STT,Hot=@Hot Where ID=@ID";
            bool st = false;
            clsDAConnection conn = new clsDAConnection();
            conn.openCon();
            using (SqlCommand com = conn.getCon().CreateCommand())
            {
                com.CommandType = CommandType.Text;
                com.CommandText = sql;

                // Create a SqlParameter object for the title parameter.
                SqlParameter p1 = com.CreateParameter();
                p1.ParameterName = "@TenTheLoai";
                p1.SqlDbType = SqlDbType.NVarChar;
                p1.Size = 50;
                p1.Value = dm.TenTheLoai;
                com.Parameters.Add(p1);

                // Use a shorthand syntax to add the id parameter.
                com.Parameters.Add("@MaDanhMuc", SqlDbType.Int).Value = dm.MyDanhMuc;
                com.Parameters.Add("@MaQuocGia", SqlDbType.Int).Value = dm.MyQuocGia;
                com.Parameters.Add("@Mota", SqlDbType.NVarChar, 1000).Value = dm.MoTa;
                com.Parameters.Add("@STT", SqlDbType.Int).Value = dm.Stt;
                com.Parameters.Add("@Hot", SqlDbType.Int).Value = dm.Hot;
                com.Parameters.Add("@ID", SqlDbType.Int).Value = dm.Id;
                // Execute the command and process the result.
                if (com.ExecuteNonQuery() == 1)
                {
                    st = true;
                }
                conn.closeCon();
                return st;
            }
        }
        public bool DeleteTheLoai(clsTheLoai dm)
        {
            string sql = "Delete from tblTheLoai Where ID=@ID";
            bool st = false;
            clsDAConnection conn = new clsDAConnection();
            conn.openCon();
            using (SqlCommand com = conn.getCon().CreateCommand())
            {
                com.CommandType = CommandType.Text;
                com.CommandText = sql;              
                com.Parameters.Add("@ID", SqlDbType.Int).Value = dm.Id;
                // Execute the command and process the result.
                if (com.ExecuteNonQuery() == 1)
                {
                    st = true;
                }
                conn.closeCon();
                return st;
            }
        }

    }
}
